---
sidebar_label: Created_at / updated_at timestamps
sidebar_position: 4
description: Add created at / updated at timestamps for Postgres in Hasura
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - default value
  - timestamps
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Thumbnail from '@site/src/components/Thumbnail';

# Postgres: Adding created_at / updated_at Timestamps

## Introduction

We often need `created_at` and `updated_at` timestamp fields in our tables in order to indicate when an object was
created or last updated. This page explains how to add these.

## Add a created_at timestamp

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

On the Hasura Console, click on the `Modify` tab of a table. When clicking on the `+Frequently used columns` button,
choose `created_at`:

<Thumbnail src="/img/schema/created-at.png" alt="Add a created_at time on the Hasura Console" width="1000px" />

Click the `Add column` button.

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
following SQL statement to the `up.sql` file:

```plsql
ALTER TABLE ONLY "public"."article" ADD COLUMN "created_at" TIMESTAMP DEFAULT NOW();
```

Add the following statement to the `down.sql` file in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

```plsql
ALTER TABLE article DROP COLUMN created_at;
```

Apply the migration and reload the metadata:

```bash
hasura migrate apply
hasura metadata reload
```

</TabItem>
<TabItem value="api" label="API">

You can add a `created_at` timestamp by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql) schema
API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
      "source": "<db_name>",
      "sql": "ALTER TABLE ONLY \"article\" ADD COLUMN \"created_at\" TIMESTAMP DEFAULT NOW();"
  }
}
```

</TabItem>
</Tabs>

## Add an updated_at timestamp

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

On the Hasura Console, click on the `Modify` tab of a table. When clicking on the `+Frequently used columns` button,
choose `updated_at`:

<Thumbnail src="/img/schema/updated-at.png" alt="Add an updated_at time on the Hasura Console" width="1000px" />

Click the `Add column` button.

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
below SQL statement to the `up.sql` file:

1.  Add an `updated_at` timestamp field to the `article` table.
2.  Define a [Postgres function](https://www.postgresql.org/docs/current/sql-createfunction.html) to set the
    `updated_at` field to `NOW()`.
3.  Create a [Postgres trigger](https://www.postgresql.org/docs/current/sql-createtrigger.html) to call the defined
    function whenever an article is updated.

```plsql
ALTER TABLE ONLY "public"."article"
ADD COLUMN "updated_at" TIMESTAMP DEFAULT NOW();

CREATE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE
UPDATE ON article
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
```

Add the following statement to the `down.sql` file in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

```plsql
DROP trigger set_timestamp on article;
DROP function trigger_set_timestamp();
ALTER TABLE article DROP COLUMN updated_at;
```

Apply the migration and reload the metadata:

```bash
hasura migrate apply
hasura metadata reload
```

</TabItem>
<TabItem value="api" label="API">

You can add an `updated_at` timestamp by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql)
schema API.

The below SQL statement will achieve the following:

1.  Add an `updated_at` timestamp field to the `article` table.
2.  Define a [Postgres function](https://www.postgresql.org/docs/current/sql-createfunction.html) to set the
    `updated_at` field to `NOW()`.
3.  Create a [Postgres trigger](https://www.postgresql.org/docs/current/sql-createtrigger.html) to call the defined
    function whenever an article is updated.

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
      "source": "<db_name>",
      "sql":
        "ALTER TABLE ONLY \"public\".\"article\"
        ADD COLUMN \"updated_at\" TIMESTAMP DEFAULT NOW();

        CREATE FUNCTION trigger_set_timestamp()
        RETURNS TRIGGER AS $$
        BEGIN
          NEW.updated_at = NOW();
        RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;

        CREATE TRIGGER set_timestamp
        BEFORE
        UPDATE ON article
        FOR EACH ROW
        EXECUTE PROCEDURE trigger_set_timestamp();"
  }
}
```

</TabItem>
</Tabs>
